package com.stage.helper;

import com.stage.annotation.Mapping;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

/**
 * Created by 刘东坡 on 2017/3/24.
 */
public class DataBaseManager {
    ObjectManager objectManager = new ObjectManager();

    /**
     * 连接数据库
     *
     * @param dataBaseName
     */
    public Connection connectDataBase(String dataBaseName) {
        String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String dbURL = "jdbc:sqlserver://localhost:1433;DatabaseName=" + dataBaseName;
        String userName = "sa";
        String userPwd = "wangleba12000";
        try {
            Class.forName(driverName);
            Connection dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
            return dbConn;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public boolean addData(Connection connection, String tableName, Object object) {
        boolean isDefine = false;
        try {
            Statement statement = connection.createStatement();
            try {
                DatabaseMetaData m_DBMetaData = connection.getMetaData();
                ResultSet tableRet = m_DBMetaData.getTables(null, null, null, new String[]{"TABLE"});
                while (tableRet.next()) {
                    if (tableName.equals(tableRet.getString("TABLE_NAME"))) {
                        isDefine = true;
                    }
                }
                if (isDefine) {
                    return this.insert(connection, object, tableName);
                } else {
                    createTableForObject(connection, tableName, object, Mapping.class);
                    addData(connection, tableName, object);
                    return true;
                }
            } finally {
                statement.close();
            }
        } catch (SQLException e) {
            return false;
        }
    }

    public boolean createTableForObject(Connection connection, String tableName, Object object, Class annotation) {
        try {
            Statement statement = connection.createStatement();
            try {
                StringBuffer sql = new StringBuffer();
                sql.append("CREATE TABLE " + tableName + "(Id      int     identity(1,1) primary key,");
                HashMap<String, String> mappings = this.getMspping(object);
                Iterator<Map.Entry<String, String>> entryKeyIterator = mappings.entrySet().iterator();
                while (entryKeyIterator.hasNext()) {
                    Map.Entry<String, String> e = entryKeyIterator.next();
                    sql.append(e.getKey() + "     " + e.getValue() + ",");
                }
                sql.append(");");
                statement.execute(sql.toString());
                return true;
            } finally {
                statement.close();
            }
        } catch (Exception e) {
            return false;
        }
    }

    private HashMap<String, String> getMspping(Object object) {
        HashMap<String, String> result = new HashMap<>();
        Class<?> tempClass = null;
        try {
            tempClass = Class.forName(object.getClass().getName());
            object = tempClass.newInstance();
        } catch (ClassNotFoundException | InstantiationException | IllegalAccessException e) {
            e.printStackTrace();
        }
        Field fields[] = tempClass.getDeclaredFields();
        for (Field field : fields) {
            Mapping mapping = (Mapping) objectManager.getFieldAnnotation(Mapping.class, object.getClass().getName(), field.getName());
            if (mapping != null) {
                result.put(mapping.name(), mapping.type());
            }
        }
        return result;
    }

    public boolean insert(Connection conn, Object object, String tableName) {
        try {
            Statement statement = conn.createStatement();
            try {
                HashMap<String, String> data = this.getDataByMapping(object);
                StringBuffer sql = new StringBuffer();
                sql.append("INSERT INTO  " + tableName + "(");
                Iterator<Map.Entry<String, String>> entryKeyIterator = data.entrySet().iterator();
                while (entryKeyIterator.hasNext()) {
                    Map.Entry<String, String> e = entryKeyIterator.next();
                    if (entryKeyIterator.hasNext()) {
                        sql.append(e.getKey() + ",");
                    } else {
                        sql.append(e.getKey());
                    }
                }
                sql.append(") " + " VALUES (");
                entryKeyIterator = data.entrySet().iterator();
                while (entryKeyIterator.hasNext()) {
                    Map.Entry<String, String> e = entryKeyIterator.next();
                    if (entryKeyIterator.hasNext()) {
                        sql.append("'" + e.getValue() + "'" + ",");
                    } else {
                        sql.append("'" + e.getValue() + "'");
                    }

                }
                sql.append(");");
                statement.execute(sql.toString());
                return true;
            } finally {
                statement.close();
            }
        } catch (SQLException e) {
            return false;
        }
    }

    private HashMap<String, String> getDataByMapping(Object object) {
        Class<?> tempClass = null;
        try {
            tempClass = Class.forName(object.getClass().getName());
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        Field fields[] = tempClass.getDeclaredFields();
        HashMap<String, String> result = new HashMap<>();
        for (Field field : fields) {
            field.setAccessible(true);
            Mapping mapping = (Mapping) objectManager.getFieldAnnotation(Mapping.class, object.getClass().getName(), field.getName());
            if (mapping != null) {
                try {
                    result.put(mapping.name(), field.get(object).toString());
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    public boolean deleteDataById(Connection connection, String tableName, int id) {
        try {
            Statement statement = connection.createStatement();
            try {
                StringBuffer sql = new StringBuffer();
                sql.append("DELETE FROM  " + tableName);
                sql.append("  WHERE Id = " + id);
                statement.execute(sql.toString());
                return true;
            } finally {
                statement.close();
            }
        } catch (SQLException e) {
            return false;
        }
    }

    public boolean updateDataById(Connection connection, String tableName, int id, Object object) {
        try {
            Statement statement = connection.createStatement();
            try {
                StringBuffer sql = new StringBuffer();
                sql.append("UPDATE " + tableName);
                sql.append("  SET ");
                HashMap<String, String> data = this.getDataByMapping(object);
                Iterator<Map.Entry<String, String>> entryKeyIterator = data.entrySet().iterator();
                while (entryKeyIterator.hasNext()) {
                    Map.Entry<String, String> e = entryKeyIterator.next();
                    if (entryKeyIterator.hasNext()) {
                        if (e.getValue().equals("true")) {
                            sql.append(e.getKey() + " = " + 1 + ",");
                        } else if (e.getValue().equals("false")) {
                            sql.append(e.getKey() + " = " + 0 + ",");
                        } else {
                            sql.append(e.getKey() + " = " + "'" + e.getValue() + "'" + ",");
                        }
                    } else {

                        if (e.getValue().equals("true")) {
                            sql.append(e.getKey() + " = " + 1);
                        } else if (e.getValue().equals("false")) {
                            sql.append(e.getKey() + " = " + 0);
                        } else {
                            sql.append(e.getKey() + " = " + "'" + e.getValue() + "'");
                        }
                    }
                }
                sql.append(" WHERE Id = " + id);
                statement.execute(sql.toString());
                return true;
            } finally {
                statement.close();
            }
        } catch (SQLException e) {
            return false;
        }
    }

    public HashMap<Object, String> queryDataFromTable(Connection connection, String tableName, String className) {
        HashMap<Object, String> result = new HashMap<>();
        try {
            Statement statement = connection.createStatement();
            try {
                StringBuffer sql = new StringBuffer();
                sql.append("SELECT *FROM " + tableName);
                ResultSet rs = statement.executeQuery(sql.toString());
                while (rs.next()) {
                    Object object = null;
                    Class<?> tempClass = null;
                    try {
                        tempClass = Class.forName(className);
                        object = tempClass.newInstance();
                    } catch (ClassNotFoundException | InstantiationException | IllegalAccessException e) {
                        e.printStackTrace();
                    }
                    assert tempClass != null;
                    Field fields[] = tempClass.getDeclaredFields();
                    for (Field field : fields) {
                        field.setAccessible(true);
                        Mapping mapping = (Mapping) objectManager.getFieldAnnotation(Mapping.class, object.getClass().getName(), field.getName());
                        try {
                            field.set(object, rs.getObject(mapping.name()));
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        }
                    }
                    result.put(object, rs.getObject("Id").toString());
                }
                return result;
            } finally {
                statement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
}
